/**
 * 测试连续上涨/下跌算法 - 直接从数据库查询
 */
const mysql = require('mysql2/promise');

// 创建数据库连接
const conn = mysql.createConnection({
    host: '182.160.6.140',
    user: 'root',
    password: 'BinanceRoot2023',
    database: 'binance_klines'
});

// 使用SQL查询获取连续涨跌统计
async function getConsecutiveStatsUsingSQL(symbol, interval, startTime, endTime) {
  const connection = await conn;
  try {
    const tableName = `kline_${interval}`;
    
    // 查询连续上涨数据
    const [upResults] = await connection.query(
      `SELECT count, COUNT(*) as occurrences 
       FROM (
         SELECT 
           @streak := IF(@prev_symbol = BINARY symbol AND @prev_close < close_price, 
                        @streak + 1, 
                        IF(@prev_symbol = BINARY symbol AND @prev_close >= close_price, 0, 1)
                      ) AS count,
           @prev_symbol := symbol, 
           @prev_close := close_price
         FROM ${tableName}, 
           (SELECT @streak := 0, @prev_symbol := '', @prev_close := 0) AS vars
         WHERE symbol = ? 
           AND open_time BETWEEN ? AND ?
         ORDER BY symbol, open_time
       ) AS streaks 
       WHERE count >= 2
       GROUP BY count`,
      [symbol, startTime, endTime]
    );
    
    // 查询连续下跌数据
    const [downResults] = await connection.query(
      `SELECT count, COUNT(*) as occurrences 
       FROM (
         SELECT 
           @streak := IF(@prev_symbol = BINARY symbol AND @prev_close > close_price, 
                        @streak + 1, 
                        IF(@prev_symbol = BINARY symbol AND @prev_close <= close_price, 0, 1)
                      ) AS count,
           @prev_symbol := symbol, 
           @prev_close := close_price
         FROM ${tableName}, 
           (SELECT @streak := 0, @prev_symbol := '', @prev_close := 0) AS vars
         WHERE symbol = ? 
           AND open_time BETWEEN ? AND ?
         ORDER BY symbol, open_time
       ) AS streaks 
       WHERE count >= 2
       GROUP BY count`,
      [symbol, startTime, endTime]
    );
    
    // 格式化结果
    const upStats = {};
    upResults.forEach(row => {
      upStats[row.count] = row.occurrences;
    });
    
    const downStats = {};
    downResults.forEach(row => {
      downStats[row.count] = row.occurrences;
    });
    
    return {
      up: upStats,
      down: downStats
    };
  } finally {
    await conn.end();
  }
}

// 获取昨天的K线数据直接从数据库
async function fetchYesterdayData(symbol, interval) {
  const connection = await conn;
  try {
    const tableName = `kline_${interval}`;
    
    // 获取当前日期
    const now = new Date();
    
    // 计算昨天的开始和结束时间戳（毫秒）
    const yesterday = new Date(now);
    yesterday.setDate(now.getDate() - 1);
    yesterday.setHours(0, 0, 0, 0);
    const startTime = yesterday.getTime();
    
    const today = new Date(now);
    today.setHours(0, 0, 0, 0);
    const endTime = today.getTime();
    
    console.log('时间范围:', new Date(startTime).toISOString(), '至', new Date(endTime).toISOString());
    
    // 查询K线数据
    const [rows] = await connection.query(
      `SELECT open_time, open_price, high_price, low_price, close_price, volume 
       FROM ${tableName} 
       WHERE symbol = ? 
       AND open_time >= ? 
       AND open_time < ? 
       ORDER BY open_time ASC`,
      [symbol, startTime, endTime]
    );
    
    // 转换为前端期望的格式 (二维数组)
    const klineData = rows.map(row => [
      row.open_time.toString(),
      row.open_price.toString(),
      row.high_price.toString(),
      row.low_price.toString(),
      row.close_price.toString(),
      row.volume.toString()
    ]);
    
    return klineData;
  } finally {
    await conn.end();
  }
}

// 计算连续涨跌 (使用JavaScript实现与前端一致的逻辑)
function calculateConsecutive(data) {
  // 初始化连续上涨/下跌统计（从1次开始到20次）
  const continuousUp = Array(21).fill(0);
  const continuousDown = Array(21).fill(0);
  
  // 按时间顺序排序数据（从早到晚）
  const sortedData = [...data].sort((a, b) => parseInt(a[0]) - parseInt(b[0]));
  
  // 前一根K线的收盘价
  let prevClose = null;
  
  // 当前连续状态
  let currentUpStreak = 0;
  let currentDownStreak = 0;
  
  // 记录每个连续序列的详细信息，方便调试
  const consecutiveSequences = [];
  
  // 遍历每根K线
  for (let i = 0; i < sortedData.length; i++) {
    const candle = sortedData[i];
    const time = new Date(parseInt(candle[0])).toISOString();
    const close = parseFloat(candle[4]);
    
    // 计算连续趋势
    if (prevClose !== null) {
      const change = ((close - prevClose) / prevClose * 100).toFixed(2);
      
      if (close > prevClose) {
        // 连续上涨
        currentUpStreak++;
        console.log(`[${time}] 收盘价: ${close}, 涨幅: ${change}%, 连续上涨: ${currentUpStreak}次`);
        
        // 如果之前有连续下跌，记录它（至少2次才记录）
        if (currentDownStreak >= 2) {
          console.log(`记录连续下跌: ${currentDownStreak}次`);
          continuousDown[currentDownStreak]++;
          consecutiveSequences.push({
            type: 'down',
            count: currentDownStreak,
            endIndex: i - 1
          });
        }
        // 重置下跌计数
        currentDownStreak = 0;
      } else if (close < prevClose) {
        // 连续下跌
        currentDownStreak++;
        console.log(`[${time}] 收盘价: ${close}, 跌幅: ${change}%, 连续下跌: ${currentDownStreak}次`);
        
        // 如果之前有连续上涨，记录它（至少2次才记录）
        if (currentUpStreak >= 2) {
          console.log(`记录连续上涨: ${currentUpStreak}次`);
          continuousUp[currentUpStreak]++;
          consecutiveSequences.push({
            type: 'up',
            count: currentUpStreak,
            endIndex: i - 1
          });
        }
        // 重置上涨计数
        currentUpStreak = 0;
      } else {
        // 平盘，中断连续计数
        console.log(`[${time}] 收盘价: ${close}, 涨跌幅: ${change}%, 平盘`);
        
        // 如果之前有连续上涨或下跌，记录它们
        if (currentUpStreak >= 2) {
          console.log(`记录连续上涨: ${currentUpStreak}次`);
          continuousUp[currentUpStreak]++;
          consecutiveSequences.push({
            type: 'up',
            count: currentUpStreak,
            endIndex: i - 1
          });
        }
        if (currentDownStreak >= 2) {
          console.log(`记录连续下跌: ${currentDownStreak}次`);
          continuousDown[currentDownStreak]++;
          consecutiveSequences.push({
            type: 'down',
            count: currentDownStreak,
            endIndex: i - 1
          });
        }
        // 重置计数
        currentUpStreak = 0;
        currentDownStreak = 0;
      }
    } else {
      console.log(`[${time}] 第一根K线，收盘价: ${close}`);
    }
    
    // 更新前一根K线的收盘价
    prevClose = close;
  }
  
  // 处理最后一组连续K线
  if (currentUpStreak >= 2) {
    console.log(`记录最后一组连续上涨: ${currentUpStreak}次`);
    continuousUp[currentUpStreak]++;
    consecutiveSequences.push({
      type: 'up',
      count: currentUpStreak,
      endIndex: sortedData.length - 1
    });
  }
  if (currentDownStreak >= 2) {
    console.log(`记录最后一组连续下跌: ${currentDownStreak}次`);
    continuousDown[currentDownStreak]++;
    consecutiveSequences.push({
      type: 'down',
      count: currentDownStreak,
      endIndex: sortedData.length - 1
    });
  }
  
  // 打印连续序列的详细信息
  console.log('\n连续序列详情:');
  for (let i = 0; i < consecutiveSequences.length; i++) {
    const seq = consecutiveSequences[i];
    const endIndex = seq.endIndex;
    const startIndex = endIndex - seq.count + 1;
    
    console.log(`序列${i+1}: ${seq.count}次连续${seq.type === 'up' ? '上涨' : '下跌'}`);
    for (let j = startIndex; j <= endIndex; j++) {
      const candle = sortedData[j];
      const time = new Date(parseInt(candle[0])).toISOString();
      const open = parseFloat(candle[1]);
      const close = parseFloat(candle[4]);
      const change = ((close - open) / open * 100).toFixed(2);
      console.log(`  [${time}] 开盘:${open} 收盘:${close} 涨跌幅:${change}%`);
    }
  }
  
  // 修正连续统计的逻辑
  for (let i = 20; i > 2; i--) {
    // 连续i次的K线也是连续(i-1)次的一部分
    continuousUp[i-1] += continuousUp[i];
    continuousDown[i-1] += continuousDown[i];
  }
  
  // 转换为对象格式
  const upStats = {};
  const downStats = {};
  
  for (let i = 2; i <= 20; i++) {
    if (continuousUp[i] > 0) {
      upStats[i] = continuousUp[i];
    }
    if (continuousDown[i] > 0) {
      downStats[i] = continuousDown[i];
    }
  }
  
  return {
    up: upStats,
    down: downStats
  };
}

// 运行测试
async function runTest() {
  try {
    const symbol = 'BTCUSDT';
    const interval = '15m';
    
    console.log(`测试 ${symbol} ${interval} 的连续涨跌统计...`);
    
    // 获取当前日期
    const now = new Date();
    
    // 计算昨天的开始和结束时间戳（毫秒）
    const yesterday = new Date(now);
    yesterday.setDate(now.getDate() - 1);
    yesterday.setHours(0, 0, 0, 0);
    const startTime = yesterday.getTime();
    
    const today = new Date(now);
    today.setHours(0, 0, 0, 0);
    const endTime = today.getTime();
    
    // 1. 使用SQL直接查询统计结果
    console.log('方法1: 使用SQL直接查询统计结果');
    const sqlStats = await getConsecutiveStatsUsingSQL(symbol, interval, startTime, endTime);
    console.log('SQL查询连续上涨统计:', sqlStats.up);
    console.log('SQL查询连续下跌统计:', sqlStats.down);
    
    // 2. 获取数据后用JavaScript计算
    console.log('\n方法2: 使用JavaScript计算统计结果');
    const klineData = await fetchYesterdayData(symbol, interval);
    console.log(`获取到 ${klineData.length} 根K线数据`);
    
    const jsStats = calculateConsecutive(klineData);
    console.log('JavaScript计算连续上涨统计:', jsStats.up);
    console.log('JavaScript计算连续下跌统计:', jsStats.down);
    
    // 3. 比较两种方法的结果
    console.log('\n比较两种方法的结果:');
    console.log('上涨统计比较:', JSON.stringify(sqlStats.up) === JSON.stringify(jsStats.up) ? '一致' : '不一致');
    console.log('下跌统计比较:', JSON.stringify(sqlStats.down) === JSON.stringify(jsStats.down) ? '一致' : '不一致');
    
    // 如果不一致，显示详细差异
    if (JSON.stringify(sqlStats.up) !== JSON.stringify(jsStats.up)) {
      console.log('上涨统计差异:');
      const allUpKeys = [...new Set([...Object.keys(sqlStats.up), ...Object.keys(jsStats.up)])].sort();
      for (const key of allUpKeys) {
        const sqlValue = sqlStats.up[key] || 0;
        const jsValue = jsStats.up[key] || 0;
        if (sqlValue !== jsValue) {
          console.log(`  ${key}次连续: SQL=${sqlValue}, JS=${jsValue}, 差异=${jsValue - sqlValue}`);
        }
      }
    }
    
    if (JSON.stringify(sqlStats.down) !== JSON.stringify(jsStats.down)) {
      console.log('下跌统计差异:');
      const allDownKeys = [...new Set([...Object.keys(sqlStats.down), ...Object.keys(jsStats.down)])].sort();
      for (const key of allDownKeys) {
        const sqlValue = sqlStats.down[key] || 0;
        const jsValue = jsStats.down[key] || 0;
        if (sqlValue !== jsValue) {
          console.log(`  ${key}次连续: SQL=${sqlValue}, JS=${jsValue}, 差异=${jsValue - sqlValue}`);
        }
      }
    }
    
  } catch (error) {
    console.error('测试失败:', error);
  }
}

// 执行测试
runTest(); 